﻿//-----------------------------------------------------------------------
// <copyright file="EntityProvider.cs" company="NECSV">
//     Copyright (c) by La Minh Thuận.
// </copyright>
// <author>thuanla1985@hotmail.com</author>
//-----------------------------------------------------------------------
namespace LMT.Db
{
    using System;
    using System.Collections.Generic;
    using System.Reflection;
    using System.Text;
    using System.Data.Common;
    using System.Reflection.Emit;

    /// <summary>
    /// The helper class uses for getting and setting value from database to entity object.
    /// </summary>
    public abstract class EntityProvider
    {
        /// <summary>
        /// the sql for inserting data
        /// </summary>
        public readonly string SqlInsert = "INSERT INTO {0} ({1}) VALUES ({2})";

        /// <summary>
        /// the sql for updating data
        /// </summary>
        public readonly string SqlUpdate = "UPDATE {0} SET {1} WHERE {2}";

        /// <summary>
        /// the sql for selecting all data
        /// </summary>
        public readonly string SqlSelectAll = "SELECT {0} FROM {1}";

        /// <summary>
        /// the sql for selecting data by primary keys
        /// </summary>
        public readonly string SqlSelectCondition = "SELECT {0} FROM {1} WHERE {2}";

        /// <summary>
        /// the sql for deleting data by primary keys
        /// </summary>
        public readonly string SqlDelete = "DELETE FROM {0} WHERE {1}";

        /// <summary>
        /// a sql for getting last inserted record.
        /// </summary>
        public readonly string SqlSequenceValue = "SELECT {0}.NEXTVAL FROM DUAL";

        /// <summary>
        /// 
        /// </summary>
        /// <param name="target">The target.</param>
        /// <param name="value">The value.</param>
        private delegate void GenericSetter<T>(T target, object value) where T : IDbEntity;

        /// <summary>
        /// 
        /// </summary>
        private Dictionary<Type, Dictionary<string, Delegate>> dynamicSetterDict;

        /// <summary>
        /// Sets the value from DataReader into Entity object.
        /// </summary>
        /// <param name="target">The entity.</param>
        /// <param name="colMap">The col map.</param>
        /// <param name="dataReader">The oledb reader.</param>
        private void SetValue(IDbEntity target, Dictionary<string, int> colMap, DbDataReader dataReader)
        {
            // Get list of property in entity object
            PropertyInfo[] objPropers = target.GetType().GetProperties();
            if (objPropers != null && objPropers.Length > 0)
            {
                DbColumn colAtt = null;
                object value = null;
                foreach (PropertyInfo info in objPropers)
                {
                    colAtt = (DbColumn)Attribute.GetCustomAttribute(info, typeof(DbColumn), false);
                    if (colAtt != null)
                    {
                        // check datareader contains specify column
                        if (colMap.ContainsKey(colAtt.ColumName))
                        {
                            // check db value is null
                            if (!dataReader.IsDBNull(colMap[colAtt.ColumName]))
                            {
                                value = dataReader[colMap[colAtt.ColumName]];

                                if (info.PropertyType == typeof(char))
                                {
                                    info.SetValue(target, value.ToString()[0], null);
                                }
                                else
                                {
                                    info.SetValue(target, value, null);
                                }
                            }
                        }
                    }
                }
            }
        }

        /// <summary>
        /// Sets the value.
        /// </summary>
        /// <param name="objPropers">The obj propers.</param>
        /// <param name="entity">The entity.</param>
        /// <param name="cachedAtt">The cached attributes map.</param>
        /// <param name="colMap">The column name map.</param>
        /// <param name="dataReader">The oledb reader.</param>
        private void SetValue(PropertyInfo[] objPropers, IDbEntity entity, Dictionary<string, DbColumn> cachedAtt, Dictionary<string, int> colMap, DbDataReader dataReader)
        {
            if (objPropers != null && objPropers.Length > 0)
            {
                DbColumn colAtt = null;
                object value = null;
                foreach (PropertyInfo info in objPropers)
                {
                    if (cachedAtt.ContainsKey(info.Name))
                    {
                        colAtt = cachedAtt[info.Name];
                        if (colMap.ContainsKey(colAtt.ColumName))
                        {
                            value = dataReader[colMap[colAtt.ColumName]];

                            // check db value is null : !dataReader.IsDBNull(colMap[colAtt.ColumName]) is slower than DBNull
                            if (DBNull.Value != value)
                            {
                                if (info.PropertyType == typeof(char))
                                {
                                    info.SetValue(entity, value.ToString()[0], null);
                                }
                                else
                                {
                                    info.SetValue(entity, value, null);
                                }
                            }
                        }
                    }
                }
            }
        }

        /// <summary>
        /// Sets the value.
        /// </summary>
        /// <param name="objPropers">The obj propers.</param>
        /// <param name="target">The entity.</param>
        /// <param name="cachedAtt">The cached attributes map.</param>
        /// <param name="colMap">The column name map.</param>
        /// <param name="setterDict">The setter dict.</param>
        /// <param name="dataReader">The oledb reader.</param>
        private void DynamicSetValue<T>(PropertyInfo[] objPropers, T target, Dictionary<string, DbColumn> cachedAtt, Dictionary<string, int> colMap, Dictionary<string, Delegate> setterDict, DbDataReader dataReader) where T : IDbEntity
        {
            if (objPropers != null && objPropers.Length > 0)
            {
                DbColumn colAtt = null;
                object value = null;
                foreach (PropertyInfo info in objPropers)
                {
                    if (cachedAtt.ContainsKey(info.Name))
                    {
                        colAtt = cachedAtt[info.Name];
                        if (colMap.ContainsKey(colAtt.ColumName))
                        {
                            value = dataReader[colMap[colAtt.ColumName]];

                            // check db value is null : !dataReader.IsDBNull(colMap[colAtt.ColumName]) is slower than DBNull
                            if (DBNull.Value != value)
                            {
                                if (info.PropertyType == typeof(char) | info.PropertyType == typeof(char?))
                                {
                                    ((GenericSetter<T>)setterDict[info.Name])(target, value.ToString()[0]);
                                }
                                else
                                {
                                    ((GenericSetter<T>)setterDict[info.Name])(target, value);
                                }
                            }
                        }
                    }
                }
            }
        }

        /// <summary>
        /// Gets the insert parameters.
        /// </summary>
        /// <param name="entity">The entity.</param>
        /// <param name="insertSql">The insert SQL.</param>
        /// <returns>array of DataProvider.</returns>
        public DbParameter[] GetInsertParameters(IDbEntity entity, out string insertSql)
        {
            List<DbParameter> listParas = null;
            insertSql = string.Empty;

            // Get list of property in entity object
            PropertyInfo[] objPropers = entity.GetType().GetProperties();
            if (objPropers != null && objPropers.Length > 0)
            {
                StringBuilder sqlFieldBuilder = null;
                StringBuilder sqlValueBuilder = null;

                listParas = new List<DbParameter>(objPropers.Length - 1);
                DbParameter para = null;
                DbColumn colAtt = null;

                sqlFieldBuilder = new StringBuilder();
                sqlValueBuilder = new StringBuilder();

                string queryTmp = string.Empty;

                // travel each property in object
                foreach (PropertyInfo info in objPropers)
                {
                    // check column attribute of column
                    colAtt = (DbColumn)Attribute.GetCustomAttribute(info, typeof(DbColumn), false);
                    if (colAtt != null && colAtt.IsParseInsert)
                    {
                        sqlFieldBuilder.Append(colAtt.ColumName + ",");
                        queryTmp = this.OnGenerateInsertPhase(colAtt);
                        sqlValueBuilder.Append(queryTmp);

                        if (!colAtt.IsUseSysDate)
                        {
                            para = this.OnCreateParameter(colAtt, string.Empty);
                            para.Value = info.GetValue(entity, null) ?? DBNull.Value;
                            listParas.Add(para);
                        }
                    }
                }

                // combine sql string
                sqlFieldBuilder.Remove(sqlFieldBuilder.Length - 1, 1);
                sqlValueBuilder.Remove(sqlValueBuilder.Length - 1, 1);
                insertSql = string.Format(this.SqlInsert, entity.TableMapping, sqlFieldBuilder.ToString(), sqlValueBuilder.ToString());
            }

            return listParas.ToArray();
        }

        /// <summary>
        /// Gets the update parameters.
        /// </summary>
        /// <param name="entity">The entity.</param>
        /// <param name="whereCon">The where con.</param>
        /// <param name="updateSql">The update SQL.</param>
        /// <returns>
        /// array of DataProvider.
        /// </returns>
        public DbParameter[] GetUpdateParemeters(IDbEntity entity, List<Condition> whereCon, out string updateSql)
        {
            List<DbParameter> listSet = null;
            List<DbParameter> listWhere = null;
            updateSql = string.Empty;

            // Get list of property in entity object
            PropertyInfo[] objPropers = entity.GetType().GetProperties();
            if (objPropers != null && objPropers.Length > 0)
            {
                string queryTmp = string.Empty;
                StringBuilder sqlSetBuilder = null;
                StringBuilder sqlWhereBuilder = null;

                listSet = new List<DbParameter>();
                listWhere = new List<DbParameter>();
                DbParameter para = null;
                DbColumn colAtt = null;

                sqlSetBuilder = new StringBuilder();
                sqlWhereBuilder = new StringBuilder();

                foreach (PropertyInfo info in objPropers)
                {
                    if (!string.IsNullOrEmpty(entity.ExcludeSetField) && entity.ExcludeSetField.Contains(info.Name))
                    {
                        continue;
                    }

                    colAtt = (DbColumn)Attribute.GetCustomAttribute(info, typeof(DbColumn), false);
                    if (colAtt != null)
                    {
                        // in case has where for updating, so don't use Primary key
                        // in case where for updating is null, use Primary key
                        if (colAtt.IsPrimaryKey)
                        {
                            if (whereCon == null)
                            {
                                queryTmp = this.OnGenerateWherePhase(colAtt);
                                sqlWhereBuilder.Append(queryTmp);
                                para = this.OnCreateParameter(colAtt, string.Empty);
                                para.Value = info.GetValue(entity, null);
                                listWhere.Add(para);
                            }
                        }
                        else
                        {
                            if (colAtt.IsParseUpdate)
                            {
                                queryTmp = this.OnGenerateSetPhase(colAtt, "SET_");
                                sqlSetBuilder.Append(queryTmp);

                                if (!colAtt.IsUseSysDate)
                                {
                                    para = this.OnCreateParameter(colAtt, "SET_");
                                    para.Value = info.GetValue(entity, null) ?? DBNull.Value;
                                    listSet.Add(para);
                                }
                            }
                        }
                    }
                }

                if (whereCon != null)
                {
                    string whereStatement = string.Empty;
                    DbParameter[] paras = this.GetWhereParameters(entity, whereCon, string.Empty, out whereStatement);

                    sqlWhereBuilder.Append(whereStatement);
                    listWhere.AddRange(paras);
                }
                else
                {
                    sqlWhereBuilder.Remove(sqlWhereBuilder.Length - 5, 5);
                }
                
                sqlSetBuilder.Remove(sqlSetBuilder.Length - 1, 1);
                updateSql = string.Format(this.SqlUpdate, entity.TableMapping, sqlSetBuilder.ToString(), sqlWhereBuilder.ToString());
            }

            listSet.AddRange(listWhere);
            return listSet.ToArray();
        }

        /// <summary>
        /// Gets the delete parameters.
        /// </summary>
        /// <param name="entity">The entity.</param>
        /// <param name="deleteSql">The delete SQL.</param>
        /// <returns></returns>
        public DbParameter[] GetDeleteParameters(IDbEntity entity, out string deleteSql)
        {
            DbParameter[] paras = this.GetKeysParameters(entity, out deleteSql);
            deleteSql = string.Format(this.SqlDelete, entity.TableMapping, deleteSql);

            return paras;
        }

        /// <summary>
        /// Gets the select by condition.
        /// </summary>
        /// <param name="limitRecord">The limit record.</param>
        /// <param name="entity">The entity.</param>
        /// <param name="whereCon">The where con.</param>
        /// <param name="alias">The alias.</param>
        /// <param name="selectSql">The select SQL.</param>
        /// <returns>
        /// array of DataProvider
        /// </returns>
        public DbParameter[] GetSelectParemeters(IDbEntity entity, List<Condition> whereCon, string alias, out string selectSql)
        {
            DbParameter[] paras = this.GetWhereParameters(entity, whereCon, alias, out selectSql);

            string dbField = this.GetSelectFields(entity, string.Empty);
            selectSql = string.Format(this.SqlSelectCondition, dbField, entity.TableMapping, selectSql);

            return paras;
        }

        /// <summary>
        /// Gets the select key parameters.
        /// </summary>
        /// <param name="entity">The entity.</param>
        /// <param name="selectSql">The select SQL.</param>
        /// <returns></returns>
        public DbParameter[] GetSelectKeyParameters(IDbEntity entity, out string selectSql)
        {
            DbParameter[] paras = this.GetKeysParameters(entity, out selectSql);
            string dbField = this.GetSelectFields(entity, string.Empty);
            selectSql = string.Format(this.SqlSelectCondition, dbField, entity.TableMapping, selectSql);

            return paras;
        }

        /// <summary>
        /// Gets the select parameters.
        /// </summary>
        /// <param name="entity">The entity.</param>
        /// <param name="whereCon">The where con.</param>
        /// <param name="alias">The alias.</param>
        /// <param name="whereStatement">The select parameters.</param>
        /// <returns></returns>
        public DbParameter[] GetWhereParameters(IDbEntity entity, List<Condition> whereCon, string alias, out string whereStatement)
        {
            PropertyInfo property = null;
            DbColumn colAtt = null;
            DbParameter para = null;
            StringBuilder sqlFieldBuilder = new StringBuilder();
            List<DbParameter> paras = new List<DbParameter>();
            Condition condi = null;
            whereStatement = string.Empty;
            string columnAlias = string.Empty;
            string queryTmp = string.Empty;

            for (int index = 0; index < whereCon.Count; index++)
            {
                condi = whereCon[index];
                if (!string.IsNullOrEmpty(entity.ExcludeWhereField) && entity.ExcludeWhereField.Contains(condi.PropertyName))
                {
                    continue;
                }

                property = entity.GetType().GetProperty(condi.PropertyName);
                if (property != null)
                {
                    colAtt = (DbColumn)Attribute.GetCustomAttribute(property, typeof(DbColumn), false);
                    if (colAtt != null)
                    {
                        if (string.IsNullOrEmpty(alias))
                        {
                            columnAlias = colAtt.ColumName;
                        }
                        else
                        {
                            columnAlias = alias + "." + colAtt.ColumName;
                        }

                        if (index != 0)
                        {
                            sqlFieldBuilder.Append(" " + condi.Xor + " ");
                        }

                        if (condi.AndOrCompare == OrAnd.And)
                        {
                            sqlFieldBuilder.Append('(');
                        }

                        if (condi.Compare == Operator.InList | condi.Compare == Operator.NotInList)
                        {
                            queryTmp = this.OnGenerateWherePhaseIncludeValue(colAtt, condi, columnAlias);
                        }
                        else
                        {
                            queryTmp = this.OnGenerateWherePhase(colAtt, condi, columnAlias);
                        }

                        sqlFieldBuilder.Append(queryTmp);

                        // close bracket if next codintion is AND
                        if (index < whereCon.Count - 1 )
                        {
                            if (whereCon[index + 1].AndOrCompare == OrAnd.And)
                            {
                                sqlFieldBuilder.Append(')');
                            }
                        }
                        else
                        {
                            sqlFieldBuilder.Append(')');
                        }

                        if (condi.Compare == Operator.Between)
                        {
                            para = this.OnCreateParameter(colAtt, condi, condi.Value, "_1");
                            paras.Add(para);
                            para = this.OnCreateParameter(colAtt, condi, condi.Value2, "_2");
                            paras.Add(para);
                        }
                        else if (condi.Compare != Operator.InList && condi.Compare != Operator.NotInList && condi.Compare != Operator.IsNull)
                        {
                            para = this.OnCreateParameter(colAtt, condi, condi.Value, string.Empty);
                            paras.Add(para);
                        }
                    }
                }
            }

            whereStatement = sqlFieldBuilder.ToString();
            return paras.ToArray();
        }

        /// <summary>
        /// Gets the db field.
        /// </summary>
        /// <param name="entity">The entity.</param>
        /// <param name="schema">The schema.</param>
        /// <returns></returns>
        public string GetSelectFields(IDbEntity entity, string aliasName)
        {
            StringBuilder fields = new StringBuilder();
            PropertyInfo[] infos = entity.GetType().GetProperties();
            DbColumn colAtt = null;

            if (infos != null && infos.Length > 0)
            {
                foreach (PropertyInfo info in infos)
                {
                    if (!string.IsNullOrEmpty(entity.ExcludeSelectField) && entity.ExcludeSelectField.Contains(info.Name))
                    {
                        continue;
                    }

                    colAtt = Attribute.GetCustomAttribute(info, typeof(DbColumn), false) as DbColumn;
                    if (colAtt != null && colAtt.IsParseSelect)
                    {
                        if (string.IsNullOrEmpty(aliasName))
                        {
                            fields.Append(colAtt.ColumName);
                        }
                        else
                        {
                            fields.Append(aliasName + "." + colAtt.ColumName);
                        }

                        fields.Append(',');
                    }
                }

                if (fields.Length > 0)
                {
                    fields = fields.Remove(fields.Length - 1, 1);
                    return fields.ToString();
                }
            }

            return "*";
        }

        /// <summary>
        /// Called when [generate insert query].
        /// </summary>
        /// <param name="colAtt">The col att.</param>
        /// <returns></returns>
        protected abstract string OnGenerateInsertPhase(DbColumn colAtt);

        /// <summary>
        /// Called when [generate where query].
        /// </summary>
        /// <param name="colAtt">The col att.</param>
        /// <param name="paraPrefix">The para prefix.</param>
        /// <returns></returns>
        protected abstract string OnGenerateWherePhase(DbColumn colAtt);

        /// <summary>
        /// Called when [generate query string].
        /// </summary>
        /// <param name="colAtt">The col att.</param>
        /// <param name="condi">The condi.</param>
        /// <param name="columnAlias">The column alias.</param>
        /// <param name="paraPrefix">The para prefix.</param>
        /// <returns></returns>
        protected abstract string OnGenerateWherePhase(DbColumn colAtt, Condition condi, string columnAlias);

        /// <summary>
        /// Called when [generate where phase include value].
        /// </summary>
        /// <param name="colAtt">The col att.</param>
        /// <param name="condi">The condi.</param>
        /// <param name="columnAlias">The column alias.</param>
        /// <returns></returns>
        protected virtual string OnGenerateWherePhaseIncludeValue(DbColumn colAtt, Condition condi, string columnAlias)
        {
            string valuePhase = string.Empty;

            if (condi.Value as string[] != null)
            {
                string[] values = condi.Value as string[];
                foreach (string value in values)
                {
                    valuePhase += string.Format("'{0}',", value);
                }

                valuePhase = valuePhase.Remove(valuePhase.Length - 1, 1);
            }
            else if (condi.Value as int[] != null)
            {
                int[] values = condi.Value as int[];
                foreach (int value in values)
                {
                    valuePhase += string.Format("{0},", value);
                }

                valuePhase = valuePhase.Remove(valuePhase.Length - 1, 1);
            }
            else
            {
                throw new NotSupportedException("Array type is not supported.");
            }

            return string.Format(" {0} {1} ({2}) ", columnAlias, condi.OperatorQuery, valuePhase);
        }

        /// <summary>
        /// Called when [generate set query].
        /// </summary>
        /// <param name="colAtt">The col att.</param>
        /// <param name="paraPrefix">The para prefix.</param>
        /// <returns></returns>
        protected abstract string OnGenerateSetPhase(DbColumn colAtt, string paraPrefix);

        /// <summary>
        /// Creates the parameter.
        /// </summary>
        /// <param name="colAtt">The col att.</param>
        /// <param name="paraPrefix">The para prefix.</param>
        /// <returns></returns>
        protected abstract DbParameter OnCreateParameter(DbColumn colAtt, string paraPrefix);

        /// <summary>
        /// Creates the parameter.
        /// </summary>
        /// <param name="colAtt">The col att.</param>
        /// <param name="condition">The condition.</param>
        /// <param name="value">The value.</param>
        /// <param name="paraSurfix">The para surfix.</param>
        /// <returns></returns>
        protected abstract DbParameter OnCreateParameter(DbColumn colAtt, Condition condition, object value, string paraSurfix);

        /// <summary>
        /// Creates the entity list.
        /// </summary>
        /// <typeparam name="T">implement of IEntity</typeparam>
        /// <param name="dbReader">The db reader.</param>
        /// <returns>
        /// entity instance
        /// </returns>
        internal T CreateEntity<T>(DbDataReader dbReader) where T : IDbEntity
        {
            T entity = Activator.CreateInstance<T>();
            if (entity != null)
            {
                dbReader.Read();
                Dictionary<string, int> colMap = this.GetColumnDictionary(dbReader);
                this.SetValue(entity, colMap, dbReader);

                colMap.Clear();
            }

            return entity;
        }

        /// <summary>
        /// Creates the entity list.
        /// </summary>
        /// <typeparam name="T">implement of IEntity</typeparam>
        /// <param name="colMap">The col map.</param>
        /// <param name="dataReader">The oledb reader.</param>
        /// <returns>list of entity instance</returns>
        internal List<T> CreateEntityList<T>(DbDataReader dbReader) where T : IDbEntity
        {
            PropertyInfo[] catchedPropers = typeof(T).GetProperties();
            Dictionary<string, DbColumn> cachedAttMap = this.GetCustomAttribute(catchedPropers);;
            Dictionary<string, int> dbColDict = this.GetColumnDictionary(dbReader);

            if (this.dynamicSetterDict == null)
            {
                this.dynamicSetterDict = new Dictionary<Type, Dictionary<string, Delegate>>();
            }

            if (!this.dynamicSetterDict.ContainsKey(typeof(T)))
            {
                this.dynamicSetterDict.Add(typeof(T), this.GenerateDynamicSetter<T>(catchedPropers));
            }

            T target = default(T);
            List<T> lstEntity = new List<T>();
            while (dbReader.Read())
            {
                target = Activator.CreateInstance<T>();
                if (target != null)
                {
                    this.DynamicSetValue(catchedPropers, target, cachedAttMap, dbColDict, this.dynamicSetterDict[typeof(T)], dbReader);
                    lstEntity.Add(target);
                }
            }

            if (cachedAttMap != null)
            {
                cachedAttMap.Clear();
            }

            dbColDict.Clear();

            return lstEntity;
        }

        /// <summary>
        /// Gets the db field on property.
        /// </summary>
        /// <param name="entity">The entity.</param>
        /// <param name="property">The property.</param>
        /// <returns></returns>
        internal string GetDbFieldOnProperty(IDbEntity entity, string property)
        {
            PropertyInfo proInfo = entity.GetType().GetProperty(property);
            if (proInfo != null)
            {
                DbColumn colAtt = (DbColumn)Attribute.GetCustomAttribute(proInfo, typeof(DbColumn), false);
                if (colAtt != null)
                {
                    return colAtt.ColumName;
                }
                else
                {
                    return string.Empty;
                }
            }
            else
            {
                return string.Empty;
            }
        }

        /// <summary>
        /// Gets the db sequence propery of entity.
        /// </summary>
        /// <param name="entity">The entity.</param>
        /// <returns>PropertyInfo value</returns>
        protected internal abstract bool SetDbSequenceValue(IDbEntity entity, object value);

        /// <summary>
        /// Gets the custom attribute.
        /// </summary>
        /// <param name="catchedPropers">The catched propers.</param>
        /// <returns></returns>
        private Dictionary<string, DbColumn> GetCustomAttribute(PropertyInfo[] catchedPropers)
        {
            Dictionary<string, DbColumn> attributes = new Dictionary<string, DbColumn>(catchedPropers.Length);
            DbColumn colAtt = null;

            foreach (PropertyInfo info in catchedPropers)
            {
                colAtt = Attribute.GetCustomAttribute(info, typeof(DbColumn), false) as DbColumn;
                if (!attributes.ContainsKey(info.Name) && colAtt != null)
                {
                    attributes.Add(info.Name, colAtt);
                }
            }

            return attributes;
        }

        /// <summary>
        /// Gets the keys parameters.
        /// </summary>
        /// <param name="entity">The entity.</param>
        /// <param name="keySql">The key SQL.</param>
        /// <returns>array of DataProvider</returns>
        private DbParameter[] GetKeysParameters(IDbEntity entity, out string keySql)
        {
            List<DbParameter> listParas = null;
            keySql = string.Empty;

            // Get list of property in entity object
            PropertyInfo[] objPropers = entity.GetType().GetProperties();
            if (objPropers != null && objPropers.Length > 0)
            {
                StringBuilder sqlFieldBuilder = null;

                listParas = new List<DbParameter>(objPropers.Length - 1);
                DbParameter para = null;
                DbColumn colAtt = null;

                sqlFieldBuilder = new StringBuilder();

                foreach (PropertyInfo info in objPropers)
                {
                    colAtt = (DbColumn)Attribute.GetCustomAttribute(info, typeof(DbColumn), false);
                    if (colAtt != null && colAtt.IsPrimaryKey)
                    {
                        sqlFieldBuilder.Append(this.OnGenerateWherePhase(colAtt));

                        para = this.OnCreateParameter(colAtt, string.Empty);
                        para.Value = info.GetValue(entity, null);
                        listParas.Add(para);
                    }
                }

                sqlFieldBuilder.Remove(sqlFieldBuilder.Length - 5, 5);
                keySql = sqlFieldBuilder.ToString();
            }

            return listParas.ToArray();
        }

        /// <summary>
        /// Gets the column map.
        /// </summary>
        /// <param name="dataReader">The oledb reader.</param>
        /// <returns>
        /// Dictionary of string : column name and int : column index
        /// </returns>
        /// <exception cref="System.NotSupportedException"></exception>
        private Dictionary<string, int> GetColumnDictionary(DbDataReader dataReader)
        {
            Dictionary<string, int> colMap = new Dictionary<string, int>(dataReader.FieldCount);

            if (!dataReader.IsClosed)
            {
                string colName = string.Empty;
                for (int index = 0; index < dataReader.FieldCount; index++)
                {
                    colName = dataReader.GetName(index);
                    if (!colMap.ContainsKey(colName))
                    {
                        colMap.Add(colName, index);
                    }
                }
            }

            return colMap;
        }

        /// <summary>
        /// Generates the dynamic setter.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="properties">The properties.</param>
        /// <returns></returns>
        private Dictionary<string, Delegate> GenerateDynamicSetter<T>(PropertyInfo[] properties) where T : IDbEntity
        {
            Dictionary<string, Delegate> setterDict = new Dictionary<string, Delegate>(properties.Length);
            foreach (PropertyInfo pi in properties)
            {
                if (pi.CanWrite)
                {
                    setterDict.Add(pi.Name, this.CreateSetMethod<T>(pi));
                }
            }

            return setterDict;
        }

        /// <summary>
        /// Creates a dynamic setter for the property
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="propertyInfo">The property info.</param>
        /// <returns></returns>
        private GenericSetter<T> CreateSetMethod<T>(PropertyInfo propertyInfo) where T : IDbEntity
        {
            MethodInfo setMethod = propertyInfo.GetSetMethod();
            if (setMethod == null)
                return null;

            Type[] arguments = new Type[2];
            arguments[0] = typeof(T);
            arguments[1] = typeof(object);

            DynamicMethod setter = new DynamicMethod(string.Format("_Set{0}_", propertyInfo.Name), typeof(void), arguments);

            ILGenerator generator = setter.GetILGenerator();
            generator.Emit(OpCodes.Ldarg_0);
            generator.Emit(OpCodes.Ldarg_1);
            generator.Emit(OpCodes.Unbox_Any, propertyInfo.PropertyType);
            generator.EmitCall(OpCodes.Callvirt, setMethod, null);
            generator.Emit(OpCodes.Ret);

            return (GenericSetter<T>)setter.CreateDelegate(typeof(GenericSetter<T>));
        }
    }
}